package mysql

import (
	"database/sql"
	"fmt"
	"log"
)

/*
*
查询数据库
*/
func QueryData(db *sql.DB) ([]Fiction, error) {
	var fictionList []Fiction

	/*当前golang ORM框架有beego orm、gorm、xorm、gorose、sqlx等。xorm有一个定制增强版xormplus/xorm，
	使得xorm支持sql模版和类mybatis的sql配置，支持动态sql。sqlx也有一个扩展库，名字叫sqlt，使sqlx支持sql模版和类mybatis的sql配置*/
	rows, err := db.Query("SELECT fiction_id, fiction_name, auth FROM fiction")
	if err != nil {
		fmt.Println("查询数据失败：", err)
		return nil, fmt.Errorf("查询数据失败：%w", err)
	}

	defer rows.Close()

	for rows.Next() {
		var obj Fiction
		//var id int
		//var name string
		//var age int
		//err := rows.Scan(&id, &name, &age)
		//if err != nil {
		//	fmt.Println("读取数据失败：", err)
		//	return nil, fmt.Errorf("读取数据失败")
		//}
		//		fmt.Println("ID:", id, "Name:", name, "Age:", age)
		err := rows.Scan(&obj.ID, &obj.Name, &obj.Auth)
		if err != nil {
			return nil, fmt.Errorf("读取数据失败：%w", err)
		}
		fictionList = append(fictionList, obj)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历数据失败：%w", err)
	}

	return fictionList, nil // 返回User列表
}
func PageQuery(db *sql.DB, pageSize int, pageNum int) ([]Fiction, error) {
	var fictionList []Fiction
	// 计算 OFFSET
	offset := (pageNum - 1) * pageSize
	// 查询 SQL 语句，使用 LIMIT 和 OFFSET
	query := `SELECT fiction_id, fiction_name, auth FROM fiction LIMIT ? OFFSET ?`
	rows, err := db.Query(query, pageSize, offset)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	// 处理查询结果
	for rows.Next() {
		var f Fiction
		err := rows.Scan(&f.ID, &f.Name, &f.Auth)
		if err != nil {
			log.Fatal(err)
		}
		fictionList = append(fictionList, f)
	}

	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	return fictionList, nil
}

func SelectById(db *sql.DB, id int) (Fiction, error) {
	var f Fiction
	rows, err := db.Query("SELECT fiction_id, fiction_name, auth FROM fiction where fiction_id = ?", id)
	if err != nil {
		fmt.Println("查询数据失败：", err)
		return f, fmt.Errorf("查询数据失败：%w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var obj Fiction
		//当你调用 rows.Scan 时，你需要传递能够修改的变量的地址，因为 Scan 实际上是在内存中直接写入这些值。
		//如果你只传递 obj.ID、obj.Name 和 obj.auth（即它们的值），那么 Scan 将无法修改这些值，因为它只是得到了这些值的副本。
		//Java直接是 f = rows.Scan，而不是变量地址进去
		err := rows.Scan(&obj.ID, &obj.Name, &obj.Auth)
		//err := rows.Scan(obj.ID, obj.Name, obj.auth)
		if err != nil {
			return obj, fmt.Errorf("读取数据失败：%w", err)
		}
		f = obj
	}
	return f, fmt.Errorf("查询数据失败：%w", err)
}

/**插入数据*/
//前面括号入参  *sql.DB DB指针,后面括号返回
func InsertData(db *sql.DB, name string, id int) (Fiction, error) {
	var f Fiction
	stmt, err := db.Prepare("INSERT INTO fiction(fiction_id,fiction_name) VALUES(?, ?)")
	if err != nil {
		fmt.Println("插入数据失败：", err)
		return f, fmt.Errorf("查询数据失败：%w", err)
	}

	defer stmt.Close()

	result, err := stmt.Exec(id, name)
	if err != nil {
		fmt.Println("插入数据失败：", err)
		return f, fmt.Errorf("查询数据失败：%w", err)
	}

	rowAffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("获取影响的行数失败：", err)
		return f, fmt.Errorf("查询数据失败：%w", err)
	}
	rows, err := db.Query("SELECT fiction_id, fiction_name, auth FROM fiction where fiction_id = ?", id)
	if err != nil {
		fmt.Println("查询数据失败：", err)
		return f, fmt.Errorf("查询数据失败：%w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var obj Fiction
		err := rows.Scan(&obj.ID, &obj.Name, &obj.Auth)
		if err != nil {
			return f, fmt.Errorf("读取数据失败：%w", err)
		}
		f = obj
	}
	fmt.Println("成功插入", rowAffected, "行数据。")
	return f, fmt.Errorf("查询数据失败：%w", err)
}

/*
*
更新数据，传入name和age
*/
func UpdateData(db *sql.DB, name string, fType int) {
	stmt, err := db.Prepare("UPDATE fiction SET type=? WHERE fiction_name=?")
	if err != nil {
		fmt.Println("更新数据失败：", err)
		return
	}

	defer stmt.Close()

	result, err := stmt.Exec(fType, name)
	if err != nil {
		fmt.Println("更新数据失败：", err)
		return
	}

	rowAffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("获取影响的行数失败：", err)
		return
	}
	fmt.Println("成功更新", rowAffected, "行数据。")
}

/**删除数据库*/
func DeleteData(db *sql.DB, name string) {
	stmt, err := db.Prepare("DELETE FROM fiction WHERE fiction_name=?")
	if err != nil {
		fmt.Println("删除数据失败：", err)
		return
	}

	defer stmt.Close()

	result, err := stmt.Exec(name)
	if err != nil {
		fmt.Println("删除数据失败：", err)
		return
	}

	rowAffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("获取影响的行数失败：", err)
		return
	}

	fmt.Println("成功删除", rowAffected, "行数据。")

}
